--select ScenarioId, ProviderCompanyId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium, CarMakeId, CarModelId
--from tblCarInsuranceResult Group By  ScenarioId, ProviderCompanyId, CarMakeId, CarModelId
--Order By ScenarioId , CarMakeId, CarModelId, min(AnnualPremium)

--select ScenarioId, ProviderCompanyId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium
--into tblPremiumRankTemp
--from  tblCarInsuranceResult Group By  ScenarioId, ProviderCompanyId
--Order By ScenarioId, min(AnnualPremium)
Select * from tblProviderCarCoverType Where ProviderCompanyId = 29 And CXCoverTypeId = 2
Declare @_ScenarioId int
Declare @_ProviderCompanyId int
Declare @_CarMakeId int
Declare @_CarModelId int
Declare @_Position int

--Select * INTO tblCarInsuranceResult@23082010 from tblCarInsuranceResult
--select * , AnnualPremium * 0.72 As NewAnnualPremium from tblCarInsuranceResult 
--Where ScenarioId > 28 And (ProviderCompanyId = 23 OR ProviderCompanyId = 30)

--Update tblCarInsuranceResult Set AnnualPremium = AnnualPremium * 0.72 
--Where ScenarioId > 28 And (ProviderCompanyId = 23 OR ProviderCompanyId = 30)

Delete from tblPremiumRankTemp

Declare @strPremiumRankTemp varchar(2000)
Declare @strPremiumRankTempWhere varchar(200)

INSERT INTO tblPremiumRankTemp(ScenarioId, ProviderCompanyId, CXCoverTypeId, MinAnnualPremium, MaxAnnualPremium, CarMakeId, CarModelId)
SELECT ScenarioId, ProviderCompanyId, CXCoverTypeId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium, CarMakeId, CarModelId
FROM  tblCarInsuranceResult GROUP BY  ScenarioId, ProviderCompanyId, CXCoverTypeId, CarMakeId, CarModelId
ORDER BY ScenarioId, CarMakeId, CarModelId, min(AnnualPremium)

--Select distinct top 10 ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, MinAnnualPremium from tblPremiumRankTemp Order By ScenarioId, CarMakeId, CarModelId, MinAnnualPremium
--Select top 10 * from tblPremiumRankTemp
--Select top 2 ProviderCompanyId from tblPremiumRankTemp Where ScenarioId = 1

Declare @ScenarioId int
Declare @CXCoverTypeId int
Declare @CarMakeId int
Declare @CarModelId int
Declare @ScenarioCount int
Declare @MinAnnualPremium float
Declare @RankTempId bigint
--Select @ScenarioCount = Count(ScenarioId) from tblPremiumRankTemp



Declare curScenario cursor for
Select ScenarioId, CarMakeId, CarModelId from tblPremiumRankTemp Order By ScenarioId, CarMakeId, CarModelId

--Select * into tblPremiumRankByCompanyTemp@20082010 from tblPremiumRankByCompanyTemp
--Select * into tblPremiumRankByCarMakeModel@20082010 from tblPremiumRankByCarMakeModel
--Select * into tblPremiumRankTemp@20082010 from tblPremiumRankTemp


--Select * from tblPremiumRankByCompanyTemp
--Select * from tblPremiumRankByCarMakeModel
--Select top 10 * from tblPremiumRankTemp

Delete from tblPremiumRankByCompanyTemp
Delete from tblPremiumRankByCarMakeModel

Insert Into tblPremiumRankByCompanyTemp(ProviderCompanyId, CXCoverTypeId, FirstCount, SecondCount, ThirdCount, FourthCount, FifthCount, SixthCount, SeventhCount)
Select distinct ProviderCompanyId, CXCoverTypeId, 0, 0, 0, 0, 0, 0, 0 from tblPremiumRankTemp

--Select * from tblPremiumRankByCompanyTemp
Set @ScenarioCount = 0

Open curScenario 
		fetch next from curScenario into @ScenarioId, @CarMakeId, @CarModelId
		
		WHILE (@@fetch_status <> -1)
		BEGIN
			Declare curPremiumRank cursor for
			Select ProviderCompanyId, CXCoverTypeId from tblPremiumRankTemp 
			Where ScenarioId = @ScenarioId And CarMakeId = @CarMakeId And CarModelId = @CarModelId
			Order By RankTempId
			
			Select @ScenarioCount = @ScenarioCount + COUNT(ProviderCompanyId) from tblPremiumRankTemp Where ScenarioId = @ScenarioId And CarMakeId = @CarMakeId And CarModelId = @CarModelId
			
			Declare @ProviderCompanyId bigint
			
			Open curPremiumRank
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FirstCount = FirstCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 1)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set SecondCount = SecondCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 2)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set ThirdCount = ThirdCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 3)
			END				
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FourthCount = FourthCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 4)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FifthCount = FifthCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
			
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 5)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set SixthCount = SixthCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
			
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 6)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId, @CXCoverTypeId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set SeventhCount = SeventhCount + 1 Where ProviderCompanyId = @ProviderCompanyId And CXCoverTypeId = @CXCoverTypeId
			
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CXCoverTypeId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CXCoverTypeId, @CarMakeId, @CarModelId, 7)
			END
			
			Close curPremiumRank
			Deallocate curPremiumRank
			
			fetch next from curScenario into @ScenarioId, @CarMakeId, @CarModelId
		END
Close curScenario
Deallocate curScenario

Select @ScenarioCount

Select ProviderCompanyId, CXCoverTypeId,
(Convert(float,FirstCount)/(@ScenarioCount/5) * 100) as FirstPercent, 
(Convert(float,SecondCount)/(@ScenarioCount/5) * 100) as SecondPercent,
(Convert(float,ThirdCount)/(@ScenarioCount/5) * 100) as ThirdPercent,
(Convert(float,FourthCount)/(@ScenarioCount/5) * 100) as FourthPercent,
(Convert(float,FifthCount)/(@ScenarioCount/5) * 100) as FifthPercent,
(Convert(float,SixthCount)/(@ScenarioCount/5) * 100) as SixthPercent,
(Convert(float,SeventhCount)/(@ScenarioCount/5) * 100) as SeventhPercent
from dbo.tblPremiumRankByCompanyTemp

Select ProviderCompanyId, CXCoverTypeId,
(Convert(float,FirstCount)/56) as FirstPercent, 
(Convert(float,SecondCount)/56) as SecondPercent,
(Convert(float,ThirdCount)/56) as ThirdPercent,
(Convert(float,FourthCount)/56) as ThirdPercent, 
(Convert(float,FifthCount)/56) as ThirdPercent,
(Convert(float,SixthCount)/56) as ThirdPercent,
(Convert(float,SeventhCount)/56) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

--Scenario Count 51184
--Company Count 7
Select ProviderCompanyId, CXCoverTypeId,
(Convert(float,FirstCount)/(51184/7)) as FirstPercent, 
(Convert(float,SecondCount)/(51184/7)) as SecondPercent,
(Convert(float,ThirdCount)/(51184/7)) as ThirdPercent,
(Convert(float,FourthCount)/(51184/7)) as ThirdPercent,
(Convert(float,FifthCount)/(51184/7)) as ThirdPercent,
(Convert(float,SixthCount)/(51184/7)) as ThirdPercent,
(Convert(float,SeventhCount)/(51184/7)) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

Select ProviderCompanyId, CXCoverTypeId, Convert(float,SUM(Position))/(51184/7) from tblPremiumRankByCarMakeModel
Group By ProviderCompanyId, CXCoverTypeId


Select ProviderCompanyId, CXCoverTypeId,
(Convert(float,FirstCount)/56) as FirstPercent, 
(Convert(float,SecondCount)/56) as SecondPercent,
(Convert(float,ThirdCount)/56) as ThirdPercent,
(Convert(float,FourthCount)/56) as ThirdPercent, 
(Convert(float,FifthCount)/56) as ThirdPercent,
(Convert(float,SixthCount)/56) as ThirdPercent,
(Convert(float,SeventhCount)/56) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

SELECT     S.ScenarioNo, PC.CompanyName, CM.Description, CM.Body, CM.CC, R.Position
FROM         tblPremiumRankByCarMakeModel AS R INNER JOIN
                      tblCarModelInfo AS CM ON R.CarModelId = CM.CarModelId AND R.CarMakeId = CM.CarMakeId INNER JOIN
                      tblProviderCompany AS PC ON R.ProviderCompanyId = PC.ProviderCompanyId INNER JOIN
                      tblScenario AS S ON R.ScenarioId = S.ScenarioID
ORDER BY R.ScenarioId, R.CarMakeId, R.CarModelId, PC.CompanyName, R.Position

--Select * from tblPremiumRankByCompanyTemp
--Select ScenarioId, CarMakeId, CarModelId, MinAnnualPremium, ProviderCompanyId from tblPremiumRankTemp 
--Order By ScenarioId, CarMakeId, CarModelId, MinAnnualPremium, ProviderCompanyId




